<?php

namespace xhadmin;
use think\facade\Validate;
use think\facade\Cache;
use PHPExcel;
use PHPExcel_Cell;
use PHPExcel_IOFactory;

class CommonService
{
	
	 /**
     * 验证数据
     * @access protected
     * @param  array        $data     数据
     * @param  string|array $validate 验证器名或者验证规则数组
     * @return array|string|true
     */
	protected static function validate($rule,$data,$msg=[]){
		$validate = Validate::rule($rule)->message($msg);	
		if (!$validate->check($data)) {
			throw new \Exception($validate->getError());
		}
		return true;
	}
	
	 /**
     * 过滤掉空的数组
     * @access protected
     * @param  array        $data     数据
     * @return array
     */
	public static function filterEmptyArray($data = []){
		foreach( $data as $k=>$v){   
			if( !$v && $v !== 0)   
				unset( $data[$k] );   
		}
		return $data;
	}
	
	
	/**
     * 生成sql查询语句
     * @access protected
     * @param  sql     原始sql语句
     * @param  $where  查询条件
	 * @param  $limit  分页
	 * @param  $orderby  排序
     * @return array
     */
	protected static function loadList($sql,$where=[],$limit,$orderby){
		$map = [];
		foreach($where as $key=>$val){
			if(is_array($val)){
				if(stripos($sql,'join') > 0){
					if($val[1] == 'between'){
						list($start,$end) = explode(',',$val[2]);
						$map .= 'a.'.$val[0].' between '.$start.' and '.$end.' and ';
					}else{
						$map .= 'a.'.$val[0].' '.$val[1]." '".$val[2]."'".' and ';
					}
				}else{
					if($val[1] == 'between'){
						list($start,$end) = explode(',',$val[2]);
						$map .= $val[0].' between '.$start.' and '.$end.' and ';
					}else{
						$map .= $val[0].$val[1]."'".$val[2]."'".' and ';
					}
					
				}		
			}
		}
		$map .= '1=1';
		if(!false === stripos($sql,"where") ){
			$where = !empty($where) ?  ' and '.$map : '';	
		}else{
			$where = !empty($where) ?  ' where '.$map : '';	
		}
		if(stripos($sql,'join') > 0){
			$orderby = ' order by a.'.$orderby;
		}else{
			$orderby = ' order by '.$orderby;
		}
		
		$limit = ' limit '.$limit;
		
		$countWhere = preg_replace('/^select(.*) from/iUs','select count(*) as count from',$sql.$where);
		$sql .= $where.$orderby.$limit;
		$result = db()->query($sql);
		$count = db()->query($countWhere);
		return ['list'=>$result,'count'=>$count[0]['count']];
	}
	
	//时间区间筛选组合
	public static function getTimeWhere($startTime,$endTime){
		
		$where = [];
		if(!empty($startTime) && !empty($endTime)){
			$where = ['between',strtotime($startTime).','.strtotime($endTime)];
		}
		if(!empty($startTime) && empty($endTime)){
			$where = ['>',strtotime($startTime)];
		}
		if(empty($startTime) && !empty($endTime)){
			$where = ['<',strtotime($endTime)];
		}
		return $where;
	}
	
	//导入excel数据
	public static function importData($key){
		$file = explode('.', $_FILES['file_name']['name']);
		if (!in_array(end($file), array('xls'))) {
			throw new \Exception('请选择xls文件！');
			exit;
		}

		$path = $_FILES['file_name']['tmp_name'];
		if (empty($path)) {
			throw new \Exception('请选择要上传的文件！');
			exit;
		}

		set_time_limit(0);

		$ex_arr = self::importExecl($path);
		if ($ex_arr['error'] == 1) {
			throw new \Exception('导入失败');
			exit;
		}
		$escel_data_length = $ex_arr['data'][0]['Rows'] - 1; 
		if ($escel_data_length <= 0) {
			throw new \Exception('数据有误');
			exit;
		}
		$excel_data = $ex_arr['data'][0]['Content'];
		if($excel_data){ 
			return $excel_data;
		}
	}
	
	//读取excel数据
   public static function importExecl($file)
	{
		//初始化变量
		$PHPExcel = '';
		$array    = [];
		if (!file_exists($file)) {
			return array("error" => 1, 'message' => 'file not found!');
		}

		$objReader = PHPExcel_IOFactory::createReader('Excel5');
		try {
			$PHPReader = $objReader->load($file);
		} catch (Exception $e) {

		}
		if (!isset($PHPReader)) {
			return array("error" => 1, 'message' => 'read error!');
		}

		$allWorksheets = $PHPReader->getAllSheets();
		$i             = 0;
		foreach ($allWorksheets as $objWorksheet) {
			$sheetname          = $objWorksheet->getTitle();
			$allRow             = $objWorksheet->getHighestRow(); //how many rows
			$highestColumn      = $objWorksheet->getHighestColumn(); //how many columns
			$allColumn          = PHPExcel_Cell::columnIndexFromString($highestColumn);
			$array[$i]["Title"] = $sheetname;
			$array[$i]["Cols"]  = $allColumn;
			$array[$i]["Rows"]  = $allRow;
			$arr                = [];
			$isMergeCell        = []; //merge cells
			foreach ($objWorksheet->getMergeCells() as $cells) {
				foreach (PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
					$isMergeCell[$cellReference] = true;
				}
			}
			for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
				$row = [];
				for ($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++) {
					$cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
					$afCol   = PHPExcel_Cell::stringFromColumnIndex($currentColumn + 1);
					$bfCol   = PHPExcel_Cell::stringFromColumnIndex($currentColumn - 1);
					$col     = PHPExcel_Cell::stringFromColumnIndex($currentColumn);
					$address = $col . $currentRow;
					$value   = $objWorksheet->getCell($address)->getValue();
					if (substr($value, 0, 1) == '=') {
						return array("error" => 0, 'message' => 'can not use the formula!');
						exit;
					}

					if ($isMergeCell[$col . $currentRow] && $isMergeCell[$afCol . $currentRow] && !empty($value)) {
						$temp = $value;
					} elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$col . ($currentRow - 1)] && empty($value)) {
						$value = $arr[$currentRow - 1][$currentColumn];
					} elseif ($isMergeCell[$col . $currentRow] && $isMergeCell[$bfCol . $currentRow] && empty($value)) {
						$value = $temp;
					}
					$row[$currentColumn] = $value;
				}
				$arr[$currentRow] = $row;
			}
			$array[$i]["Content"] = $arr;
			$i++;
		}
		
		unset($objWorksheet);
		unset($PHPReader);
		unset($PHPExcel);
		unlink($file);
		return array("error" => 0, "data" => $array);
	}
	
	
	//导出excel表头设置
	public static function getTag($key){
	   $data = [
			'1' => 'A',
			'2' => 'B',
			'3' => 'C',
			'4' => 'D',
			'5' => 'E',
			'6' => 'F',
			'7' => 'G',
			'8' => 'H',
			'9' => 'I',
			'10'=>'J',
			'11'=>'K',
			'12'=>'L',
			'13'=>'M',
			'14'=>'N',
			'15'=>'O',
			'16'=>'P',
			'17'=>'Q',
			'18'=>'R',
			'19'=>'S',
			'20'=>'T',
			'21'=>'U',
			'22'=>'V',
			'23'=>'W',
			'24'=>'X',
			'25'=>'Y',
			'26'=>'Z',
		];
		return $data[$key];
   }
	
    
}
